var express = require('express');
var router = express.Router();
var sqlite3 = require('sqlite3').verbose();
var path = require('path');
const { addAbortSignal } = require('stream');
/* GET users listing. */
router.get('/', function(req, res, next) {
    var page = req.query.page;
    var limit = req.query.limit;
    var db=new sqlite3.Database(path.join(__dirname, "../db/LH.s3db"));
    try{
        sqlStr = "SELECT  s_id ,s_name ,sex ,score,year \
      FROM (SELECT * FROM student JOIN \
            class ON student.[class] = class.[class_name] JOIN \
            s_activity ON s_activity.[s_id] = student.[s_id] )  WHERE \
        ct_id = ?"
        if(req.query.function=='per_piechart'){
            var s_id = req.query.s_id;
            sqlStr = "select * from(SELECT \
                s_id,\
                s_activity.[a_no] ,\
                a_name , \
                manager.[m_id] ,\
                manager.[m_name] ,\
                type,\
                s_activity.[status],\
                s_time, \
                e_time ,  \
                s_activity.[time] , \
                score \
              FROM \
                (s_activity JOIN \
                activity on s_activity.[a_no] = activity.[a_no])  join manager on activity.[m_id]=manager.[m_id]) where s_id= ? ";
                db.all(sqlStr,[req.query.s_id],function(err,rows){
                    if(rows.length==0){
                        req.send('0');
                    }else{
                        table={"智育":0,"美育":0,"德育":0,"体育":0,"劳育":0}
                        for(var i=0;i<rows.length;i++){
                            if(rows[i].score!=-1){
                                table[rows[i].type]+=rows[i].score;
                            }}
                        console.log(table);
                        res.status(200).json(table);
                        return ;
                    }})
        }else{
            if(req.query.searchParams!=undefined){
                var time = req.query.searchParams;
                console.log(time);
                sqlStr += " and year = ? limit ?,?";
                param = [req.session.username,time,(page-1)*limit,limit]
            }else if(req.query.barchart=='1'){
                param = [req.session.username];
            }else{
                sqlStr+=" limit ?,?"
                param = [req.session.username,(page-1)*limit,limit];
            }
            db.all(sqlStr,param,function(err,rows){
                data = new Array();
                table = {"code":0,"msg":"","count":rows.length,"data":data};
                if(rows.length==0){
                    res.status(200).json(table);
                }else{
                    rows[0].sex=(rows[0].sex==0)?'女':'男';
                    data.push(rows[0]);
                    var cnt=0;
                    for(var i=1;i<rows.length;i++ ){
                        if(rows[i].s_id!=rows[i-1].s_id){
                            rows[i].sex=(rows[i].sex==0)?'女':'男';
                            data.push(rows[i]);
                            cnt++;
                        }else{
                            if(rows[i].score!=-1)
                                data[cnt].score+=rows[i].score;
                        }
                    }
                    res.status(200).json(table);
                }
            })
        }
    }catch(ex){
        console.log(ex);
    }
});

module.exports = router;
